CREATE DATABASE CourierManagement
GO
USE CourierManagement
GO

CREATE TABLE Department
(
	DepId int identity primary key,
	DepartmentName nvarchar(50),
	Description nvarchar(200)
)

CREATE TABLE Employee
(
	EmpId int identity primary key,
	EmployeeName nvarchar(50),
	[Address] nvarchar(200),
	Email nvarchar(50),
	Phone nvarchar(12),
	[Image] nvarchar(200),
	Position nvarchar(20),
	DepId int foreign key references Department
)


CREATE TABLE EmployeeLogin
(
	EmpId int primary key references Employee,
	Username nvarchar(50),
	[Password] nvarchar(50),
	Alias nvarchar(10),
	AliasPassword nvarchar(10),
	[Status] bit default 0,
	StandbyTime nvarchar(20),
	StandbyResume nvarchar(20),
)

CREATE TABLE Attendance
(
	Id int identity primary key,
	EmpId int foreign key references EmployeeLogin,
	UpdateTime nvarchar(20)
)

CREATE TABLE Courier
(
	CourierId int identity primary key,
	CourierName nvarchar(200),
	CustomerName nvarchar(200),
	Phone nvarchar(12),
	[Status] nvarchar(max),
	[Weight] nvarchar(20),
	DepartLocation nvarchar(200),
	Destination nvarchar(200),
	Distance nvarchar(10)
)

CREATE TABLE CourierManage
(
	Id int identity primary key,
	CourierId int foreign key references Courier,
	Sender int foreign key references Employee(EmpId),
	Receiver int foreign key references Employee(EmpId),
	Dispatcher int foreign key references Employee(EmpId),
	[Status] tinyint,
	[Description] nvarchar(max), -- Dua len giao dien phai la Ly do hoan, thay doi hoac khong gui duoc hang 
	UpdateTime nvarchar(20)
)

CREATE TABLE Administator
(
	Id int identity primary key,
	AdUser nvarchar(50),
	AdPass nvarchar(50),
	[Role] char
)

CREATE TABLE Calculation
(
	Id int identity primary key,
	CourierType nvarchar(100),
	Distance nvarchar(10),
	ChargePerGram real
)

--CREATE TABLE CourierUpdateDate
--(
--	Id int identity primary key,
--	UpdateDate nvarchar(20),
--	Username nvarchar(50) foreign key references EmployeeLogin 
--)


Insert into Department values('Phong Gui - Nhan', 'Phong Gui - Nhan')
Insert into Department values('Phong Dieu Van', 'Phong Dieu Van')

Insert into Employee values('Nguyen Van A', 'HN', 'anv@gmail.com','0986999999','nguyenvana.jpg','nhan vien','1')
Insert into Employee values('Nguyen Van B', 'HD', 'bnv@gmail.com','0986999999','nguyenvanb.jpg','nhan vien','2')
Insert into Employee values('Nguyen Van C', 'BD', 'cnv@gmail.com','0986999999','nguyenvanc.jpg','nhan vien','1')

Insert into EmployeeLogin values ('1', 'Anv','123456','Alias_A','123456','0', '','')
Insert into EmployeeLogin values ('2', 'Bnv','123456','Alias_B','123456','0', '','')

Insert into Courier values('Hang 1','Khach hang 1','098123456','','500','Ha Noi','Hai Duong','60')
Insert into Courier values('Hang 2','Khach hang 2','098123456','','300','Hai Duong','Ha Giang','120')

Insert into CourierManage values('1','1','3','2','','Gui Hang Cho Khach','')
Insert into CourierManage values('2','3','1','2','','Gui Hang Cho Khach','')

Insert into Calculation values('Hang De Vo','1','10000')
Insert into Calculation values('Hang Cong Kenh','10','10000')

Select top 1 CourierId from Courier  order by CourierId desc
select Count(CourierId) from Courier
select * from Courier